

ALTER        function fn_load_all_packingslips (@sales_order_no varchar(50),
			 @customer_po varchar(50), @customer_id int, @status varchar(50))
returns  @fn_results Table (CustomerShipping_Id int, SalesOrder_Id int,
Label varchar(50),
			SalesOrder_No varchar(50), C_Name varchar(65), 
			SO_Customer_PO_Number varchar(50), CST_Status varchar(50),
			Inv_No varchar(50))
as 
begin
	declare @quantity_shipped int
	declare @req_quantity int
	declare @customer_shipping_Id int
	declare @line_item_id int
	declare @customer_name varchar(65)
	declare @temp_sales_order_no varchar(50)
	declare @temp_sales_order_id int
	declare @temp_customer_id int
	declare @temp_customer_po varchar(50)
	declare @temp_label varchar(50)
	declare @temp_status varchar(50)
	declare @temp_inv_no varchar(50)

	declare @results Table (CustomerShipping_Id int, SalesOrder_Id int,
		Label varchar(50),SalesOrder_No varchar(50), C_Name varchar(65), 
			SO_Customer_PO_Number varchar(50), CST_Status varchar(50),
			Inv_No varchar(50))
	
	
	
	if(@sales_order_no <> '')
	declare allItemsCursor CURSOR FOR
	SELECT CustomerShipping_Id,Sales_Order.SalesOrder_ID,Label,SalesOrder_No,C_Name,
		 SO_Customer_PO_Number,CST_Status,Inv_No FROM Sales_Order,
		 Customer_Shipping_Table,Customer WHERE 
		 Sales_Order.SalesOrder_ID = Customer_Shipping_Table.SalesOrder_ID and
		 Customer.Customer_ID = Sales_Order.Customer_ID 
		 and SalesOrder_No = @sales_order_no
		 Order By Sales_Order.SalesOrder_Id
	else if(@customer_po <> '')
	declare allItemsCursor CURSOR FOR
	SELECT CustomerShipping_Id,Sales_Order.SalesOrder_ID,Label,SalesOrder_No,C_Name,
		 SO_Customer_PO_Number,CST_Status,Inv_No FROM Sales_Order,
		 Customer_Shipping_Table,Customer WHERE 
		 Sales_Order.SalesOrder_ID = Customer_Shipping_Table.SalesOrder_ID and
		 Customer.Customer_ID = Sales_Order.Customer_ID 
		 and SO_Customer_PO_Number = @customer_po
		 Order By Sales_Order.SalesOrder_Id
	else if(@customer_id <> 0)
	declare allItemsCursor CURSOR FOR
	SELECT CustomerShipping_Id,Sales_Order.SalesOrder_ID,Label,SalesOrder_No,C_Name,
		 SO_Customer_PO_Number,CST_Status,Inv_No FROM Sales_Order,
		 Customer_Shipping_Table,Customer WHERE 
		 Sales_Order.SalesOrder_ID = Customer_Shipping_Table.SalesOrder_ID and
		 Customer.Customer_ID = Sales_Order.Customer_ID 
		 and Customer.Customer_ID = @customer_id
		 and SalesOrder_No = @sales_order_no		
		 Order By Sales_Order.SalesOrder_Id

	else
	declare allItemsCursor CURSOR FOR	
	SELECT CustomerShipping_Id,Sales_Order.SalesOrder_ID,Label,SalesOrder_No,C_Name,
		 SO_Customer_PO_Number,CST_Status,Inv_No FROM Sales_Order,
		 Customer_Shipping_Table,Customer WHERE 
		 Sales_Order.SalesOrder_ID = Customer_Shipping_Table.SalesOrder_ID and
		 Customer.Customer_ID = Sales_Order.Customer_ID 		
		 Order By Sales_Order.SalesOrder_Id

	open allItemsCursor	

	fetch next from allItemsCursor into @customer_shipping_Id,@temp_sales_order_id,@temp_label,@temp_sales_order_no,@customer_name,@temp_customer_po,@temp_status,@temp_inv_no

	while @@fetch_status = 0
	begin
		  INSERT INTO @results VALUES (@customer_shipping_Id,@temp_sales_order_id,@temp_label,@temp_sales_order_no,
			@customer_name,@temp_customer_po,@temp_status,@temp_inv_no)
		fetch next from allItemsCursor into @customer_shipping_Id,@temp_sales_order_id,@temp_label,@temp_sales_order_no,@customer_name,@temp_customer_po,@temp_status,@temp_inv_no
	end	
	
	close allItemsCursor
	deallocate allItemsCursor

	declare openItemCursor CURSOR FOR
		select DISTINCT SalesOrder_Id,CustomerShipping_Id, Label,SalesOrder_No,C_Name,SO_Customer_PO_Number,CST_Status,Inv_No from
		 fn_load_all_open_packingslips(@sales_order_no,@customer_po,@customer_id) ORDER BY SalesOrder_No
	
	
	open openItemCursor

	fetch next from openItemCursor into @temp_sales_order_id,@customer_shipping_Id,@temp_label,@temp_sales_order_no,@customer_name,@temp_customer_po,@temp_status,@temp_inv_no

	while @@fetch_status = 0
	begin
		  INSERT INTO @results VALUES (@customer_shipping_Id,@temp_sales_order_id,@temp_label,@temp_sales_order_no,
			@customer_name,@temp_customer_po,@temp_status,@temp_inv_no)
		fetch next from openItemCursor into @temp_sales_order_id,@customer_shipping_Id,@temp_label,@temp_sales_order_no,@customer_name,@temp_customer_po,@temp_status,@temp_inv_no

	end	
	close openItemCursor
	deallocate openItemCursor

	if(@status = 'CANCELLED')	
		insert @fn_results select *from @results where CST_Status = 'CANCELLED'
	else if(@status = 'SHIPPED')
		insert @fn_results select *from @results where CST_Status = 'SHIPPED'
	else if(@status = 'ISSUED')
		insert @fn_results select *from @results where CST_Status = 'ISSUED'
	else
		insert @fn_results select *from @results


	return
end












